package org.jeecg.modules.cw.controller;
//http://localhost:8080/jeecg-boot/cw/cwGyjfsz/gongyong/export

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.modules.cw.entity.CwCzjb;
import org.jeecg.modules.cw.entity.CwGyjfsz;
import org.jeecg.modules.cw.entity.CwKs;
import org.jeecg.modules.cw.mapper.CwCzjbMapper;
import org.jeecg.modules.cw.mapper.CwGyjfszMapper;
import org.jeecg.modules.cw.mapper.CwKsMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/cw/cwGyjfsz/gongyong")
public class GongYongExportController {

    @Autowired
    private CwGyjfszMapper cwGyjfszMapper;

    @Autowired
    private CwKsMapper cwKsMapper;

    @Autowired
    private CwCzjbMapper cwCzjbMapper;

    // 项目数据模型（新增xmlx属性）
    private static class XmItem {
        private String name;    // 项目名称
        private String jflx;    // 经费类型："财政" 或 "非财政"
        private String xmlx;    // 新增：项目类型（用于判断是否为成果转化）
        private String jbsrze;  // 基本收入总额

        // 新增构造方法，包含xmlx
        public XmItem(String name, String jflx, String xmlx, String jbsrze) {
            this.name = name;
            this.jflx = jflx;
            this.xmlx = xmlx;  // 赋值xmlx
            this.jbsrze = jbsrze;
        }

        // Getter方法（新增xmlx的Getter）
        public String getName() {
            return name;
        }

        public String getJflx() {
            return jflx;
        }

        public String getXmlx() {
            return xmlx;
        }  // 新增：获取xmlx

        public String getJbsrze() {
            return jbsrze;
        }
    }

    private List<CwKs> getKeshiListFromDb(String year) {
        QueryWrapper<CwKs> queryWrapper = new QueryWrapper<>();
        queryWrapper.isNotNull("ksmc")
                .orderByAsc("create_time");
        // 若year不为空，添加年份筛选（支持新格式）
        if (year != null && !year.trim().isEmpty()) {
            queryWrapper.eq("nd", year.trim());
        }
        return cwKsMapper.selectList(queryWrapper);
    }

    private List<XmItem> getXmItemListFromDb(String year) {
        QueryWrapper<CwGyjfsz> queryWrapper = new QueryWrapper<>();
        // 关键修改：增加xmlx字段查询
        queryWrapper.select(
                        "xmmc",
                        "jflx",
                        "xmlx",  // 新增：查询xmlx字段
                        "SUM(jbsrze) as jbsrze"
                )
                .isNotNull("xmmc")
                .isNotNull("jflx")
                .groupBy("xmmc", "jflx", "xmlx");  // 新增：按xmlx分组
        if (year != null && !year.trim().isEmpty()) {
            queryWrapper.eq("nd", year.trim());
        }

        return cwGyjfszMapper.selectList(queryWrapper).stream()
                .map(gyjfsz -> new XmItem(
                        gyjfsz.getXmmc(),
                        gyjfsz.getJflx(),
                        gyjfsz.getXmlx(),  // 新增：传递xmlx字段
                        gyjfsz.getJbsrze() != null ? gyjfsz.getJbsrze().toString() : "0"
                ))
                .collect(Collectors.toList());
    }

    // 关键修改：新增获取"成果转化"类型数据
    private Map<String, CwCzjb> getCzjbDataMap(String year) {
        QueryWrapper<CwCzjb> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("xmlx", "财政", "非财政", "成果转化");
        // 若year不为空，添加年份筛选（支持新格式）
        if (year != null && !year.trim().isEmpty()) {
            queryWrapper.eq("nd", year.trim());
        }
        List<CwCzjb> list = cwCzjbMapper.selectList(queryWrapper);

        return list.stream()
                .collect(Collectors.toMap(
                        CwCzjb::getXmlx,
                        czjb -> czjb,
                        (existing, replacement) -> existing
                ));
    }

    @GetMapping("/export")
    public void export(HttpServletResponse response,
                       @RequestParam(required = false) String year) throws Exception {
        List<CwKs> keshiList = getKeshiListFromDb(year);
        List<XmItem> allXmItems = getXmItemListFromDb(year);
        Map<String, CwCzjb> czjbDataMap = getCzjbDataMap(year);

        // 按经费类型拆分项目
        List<XmItem> fiscalXmItems = allXmItems.stream()
                .filter(item -> "财政".equals(item.getJflx()))
                .collect(Collectors.toList());
        List<XmItem> nonFiscalXmItems = allXmItems.stream()
                .filter(item -> "非财政".equals(item.getJflx()))
                .collect(Collectors.toList());

        Map<String, CwGyjfsz> fiscalDataMap = getFinancialDataMap(fiscalXmItems, "财政", null, year); // 传入null，表示不筛选xmlx
        Map<String, CwGyjfsz> nonFiscalDataMap = getFinancialDataMap(nonFiscalXmItems, "非财政", null, year); // 传入null，表示不筛选xmlx
        // 查询成果转化项目的费用数据（仅查询 xmlx = '成果转化' 的记录）
        Map<String, CwGyjfsz> cgzhDataMap = getFinancialDataMap(nonFiscalXmItems, "非财政", "成果转化", year);

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("Sheet1");

        // 样式定义
        CellStyle titleStyle = createTitleStyle(wb);
        CellStyle groupHeaderStyle = createHeaderStyle(wb, true);
        CellStyle headerStyle = createHeaderStyle(wb, false);
        CellStyle dataStyleCenter = createDataStyle(wb, HorizontalAlignment.CENTER);
        CellStyle dataStyleLeft = createDataStyle(wb, HorizontalAlignment.LEFT);
        CellStyle descriptionStyle = createDescriptionStyle(wb);
        CellStyle totalStyle = createTotalStyle(wb); // 合计行样式

        // 列结构计算
        int baseCols = 3; // 支出类型, 经济分类科目, 合计
        int fiscalCols = 2 + fiscalXmItems.size(); // 财政列：小计 + 基本 + 财政项目
        int nonFiscalCols = 2 + nonFiscalXmItems.size(); // 非财政列：小计 + 基本 + 非财政项目
        int keshiCols = keshiList.size() * 3; // 每科室3列
        int totalCols = baseCols + fiscalCols + nonFiscalCols + keshiCols;

        int rowIdx = 0;

        // 顶部标题行
        rowIdx = writeTitleRow(sheet, rowIdx, totalCols, "附表2-公用经费收支测算附表", titleStyle);

        // 表头定义
        int headerTopRow = rowIdx; // 表头起始行
        XSSFRow headerRow1 = sheet.createRow(rowIdx++); // 第一层表头
        XSSFRow headerRow2 = sheet.createRow(rowIdx++); // 第二层表头

        // 第一层表头（支出类型、经济分类科目、合计）
        setCell(headerRow1, 0, "支出类型", groupHeaderStyle);
        setCell(headerRow1, 1, "经济分类科目", groupHeaderStyle);
        setCell(headerRow1, 2, "合计", groupHeaderStyle);
        // 合并支出类型、经济分类科目、合计列（跨两行）
        mergeRegion(sheet, headerTopRow, headerTopRow + 1, 0, 0);
        mergeRegion(sheet, headerTopRow, headerTopRow + 1, 1, 1);
        mergeRegion(sheet, headerTopRow, headerTopRow + 1, 2, 2);

        // 财政列标题（第一层）
        int fiscalStartCol = 3;
        int fiscalEndCol = fiscalStartCol + fiscalCols - 1;
        setCell(headerRow1, fiscalStartCol, "财政", groupHeaderStyle);
        mergeRegion(sheet, headerTopRow, headerTopRow, fiscalStartCol, fiscalEndCol);

        // 非财政列标题（第一层）
        int nonFiscalStartCol = fiscalEndCol + 1;
        int nonFiscalEndCol = nonFiscalStartCol + nonFiscalCols - 1;
        setCell(headerRow1, nonFiscalStartCol, "非财政", groupHeaderStyle);
        mergeRegion(sheet, headerTopRow, headerTopRow, nonFiscalStartCol, nonFiscalEndCol);

        // 科室名称（第一层表头）
        int keshiStartCol = nonFiscalEndCol + 1;
        int c = keshiStartCol;
        for (CwKs ks : keshiList) {
            setCell(headerRow1, c, ks.getKsmc(), groupHeaderStyle);
            mergeRegion(sheet, headerTopRow, headerTopRow, c, c + 2);
            c += 3;
        }

        // 第二层表头：财政子列（小计→基本→项目）
        c = fiscalStartCol;
        setCell(headerRow2, c++, "小计", headerStyle);
        setCell(headerRow2, c++, "基本", headerStyle);
        for (XmItem xm : fiscalXmItems) {
            setCell(headerRow2, c++, xm.getName(), headerStyle);
        }

        // 第二层表头：非财政子列（小计→基本→项目）
        c = nonFiscalStartCol;
        setCell(headerRow2, c++, "小计", headerStyle);
        setCell(headerRow2, c++, "基本", headerStyle);
        for (XmItem xm : nonFiscalXmItems) {
            setCell(headerRow2, c++, xm.getName(), headerStyle);
        }

        // 第二层表头：科室明细标题
        c = keshiStartCol;
        for (int i = 0; i < keshiList.size(); i++) {
            setCell(headerRow2, c++, "金额", headerStyle);
            setCell(headerRow2, c++, "测算内容", headerStyle);
            setCell(headerRow2, c++, "测算依据", headerStyle);
        }

        // 设置列宽
        setColumnWidths(sheet, fiscalStartCol, fiscalEndCol, nonFiscalStartCol, nonFiscalEndCol,
                keshiStartCol, keshiList.size());

        // 数据区域
        // 1) 基本收入总额行 - 记录行索引用于后续计算
        int basicIncomeRowIdx = rowIdx;
        rowIdx = writeBasicIncomeRow(sheet, rowIdx, dataStyleCenter, keshiStartCol,
                keshiStartCol + keshiCols - 1, fiscalStartCol, fiscalEndCol,
                nonFiscalStartCol, nonFiscalEndCol, fiscalXmItems, nonFiscalXmItems, czjbDataMap);

        // 2) 公用经费总计（先占位，后续填充）
        int totalGongYongRowIdx = rowIdx; // 记录公用经费总计行索引
        rowIdx = writeTotalRow(sheet, rowIdx, "公用经费总计", null, dataStyleCenter,
                fiscalStartCol, fiscalEndCol, nonFiscalStartCol, nonFiscalEndCol,
                keshiStartCol, keshiStartCol + keshiCols - 1, keshiList);

        // 3) 商品和服务支出组
        String[] serviceItems = {
                "办公费", "印刷费", "手续费", "水费", "电费", "邮电费", "取暖费", "物业管理费", "差旅费",
                "因公出国（境）费用", "维修（护）费", "租赁费", "会议费", "培训费", "公务接待费",
                "专用材料费", "被装购置费", "专用燃料费", "劳务费", "委托业务费", "工会经费",
                "公务用车运行维护费", "其他交通费用", "税金及附加费用", "其他商品和服务支出"
        };
        int serviceStartRow = rowIdx;
        List<Integer> serviceRowIndexes = new ArrayList<>(); // 记录行索引用于合计计算
        for (String item : serviceItems) {
            String contentHint = null;
            if ("办公费".equals(item)) contentHint = "填报明细，如办公用品，书报杂志等";
            else if ("会议费".equals(item) || "培训费".equals(item)) contentHint = "填报会议培训计划表";

            rowIdx = writeExpenseRow(sheet, rowIdx, "商品和服务支出", item, dataStyleCenter, dataStyleLeft,
                    fiscalStartCol, fiscalEndCol, nonFiscalStartCol, nonFiscalEndCol,
                    keshiStartCol, keshiList,
                    contentHint, fiscalDataMap, nonFiscalDataMap, czjbDataMap, cgzhDataMap); // 新增传入 cgzhDataMap
            serviceRowIndexes.add(rowIdx - 1);
        }
        mergeRegion(sheet, serviceStartRow, rowIdx - 1, 0, 0);
        // 商品和服务支出合计行
        int serviceTotalRowIdx = rowIdx; // 记录商品和服务支出合计行索引
        rowIdx = writeGroupTotalRow(sheet, rowIdx, "合计", null, totalStyle,
                fiscalStartCol, fiscalEndCol, nonFiscalStartCol, nonFiscalEndCol,
                keshiStartCol, keshiList.size(), serviceRowIndexes, sheet);

        // 4) 资本性支出组
        String[] capitalItems = {
                "房屋建筑物购建", "办公设备购置", "专用设备购置", "基础设施建设", "大型修缮",
                "信息网络及软件购置更新", "物资储备", "土地补偿", "安置补助",
                "地上附着物和青苗补偿", "拆迁补偿", "公务用车购置", "其他交通工具购置",
                "文物和陈列品购置", "无形资产购置", "其他资本性支出"
        };
        int capitalStartRow = rowIdx;
        List<Integer> capitalRowIndexes = new ArrayList<>(); // 记录行索引用于合计计算
        for (String item : capitalItems) {
            rowIdx = writeExpenseRow(sheet, rowIdx, "资本性支出", item, dataStyleCenter, dataStyleLeft,
                    fiscalStartCol, fiscalEndCol, nonFiscalStartCol, nonFiscalEndCol,
                    keshiStartCol, keshiList,
                    null, fiscalDataMap, nonFiscalDataMap, czjbDataMap, cgzhDataMap); // 新增传入 cgzhDataMap
            capitalRowIndexes.add(rowIdx - 1);
        }
        mergeRegion(sheet, capitalStartRow, rowIdx - 1, 0, 0);
        // 资本性支出合计行
        int capitalTotalRowIdx = rowIdx; // 记录资本性支出合计行索引
        rowIdx = writeGroupTotalRow(sheet, rowIdx, "合计", null, totalStyle,
                fiscalStartCol, fiscalEndCol, nonFiscalStartCol, nonFiscalEndCol,
                keshiStartCol, keshiList.size(), capitalRowIndexes, sheet);

        // 计算并填充公用经费总计行
        calculateAndFillTotalRow(sheet, totalGongYongRowIdx, serviceTotalRowIdx, capitalTotalRowIdx,
                fiscalStartCol, fiscalEndCol, nonFiscalStartCol, nonFiscalEndCol,
                keshiStartCol, keshiStartCol + keshiCols - 1, dataStyleCenter);

        // 5) 当年收支结余 - 记录行索引
        int balanceRowIdx = rowIdx;
        rowIdx = writeTotalRow(sheet, rowIdx, "当年收支结余", null, dataStyleCenter,
                fiscalStartCol, fiscalEndCol, nonFiscalStartCol, nonFiscalEndCol,
                keshiStartCol, keshiStartCol + keshiCols - 1, keshiList);

        // 计算并填充当年收支结余（基本收入总额 - 公用经费总计）
        calculateAndFillBalanceRow(sheet, balanceRowIdx, basicIncomeRowIdx, totalGongYongRowIdx,
                fiscalStartCol, fiscalEndCol, nonFiscalStartCol, nonFiscalEndCol,
                dataStyleCenter);

        // 6) 当年成果转化收入计提的成果转化奖
        int cgzhRowIdx = rowIdx;
        rowIdx = writeTotalRow(sheet, rowIdx, "当年成果转化收入计提的成果转化奖", null, dataStyleCenter,
                fiscalStartCol, fiscalEndCol, nonFiscalStartCol, nonFiscalEndCol,
                keshiStartCol, keshiStartCol + keshiCols - 1, keshiList);
// 计算并填充成果转化奖行
        calculateAndFillCgzhRow(sheet, cgzhRowIdx, nonFiscalStartCol, nonFiscalEndCol,
                nonFiscalXmItems, cgzhDataMap, dataStyleCenter);

        // 7) 扣成果转化收入计提的成果转化奖后结余
        int finalBalanceRowIdx = rowIdx;
        rowIdx = writeTotalRow(sheet, rowIdx, "扣成果转化收入计提的成果转化奖后结余", null, dataStyleCenter,
                fiscalStartCol, fiscalEndCol, nonFiscalStartCol, nonFiscalEndCol,
                keshiStartCol, keshiStartCol + keshiCols - 1, keshiList);
// 计算并填充扣奖后结余行
        calculateAndFillFinalBalanceRow(sheet, finalBalanceRowIdx, balanceRowIdx, cgzhRowIdx,
                fiscalStartCol, fiscalEndCol, nonFiscalStartCol, nonFiscalEndCol,
                nonFiscalXmItems, cgzhDataMap, dataStyleCenter);

        // 8) 说明行
        String desc = "说明：1、本表财政支出结余应为0，非财政收入中技术服务收入、成果转化收入、科研副产品收入均填报真实测算收入，因预算上报为平衡预算，可与预算收入明细表中的预算收入不一致，本表测算产生的收支结余仅作为后续管理参考依据；\n" +
                "2、填报举例：维修费：如维修大门3.5万，测算内容：大门维修；测算依据：说明3.5万金额是如何测算出来的；\n" +
                "3、各单位科室只需测算基本公用经费，与非财政收入相关的经费支出不按科室测算，即各科室（基本）=财政基本+非财政基本；\n" +
                "4、各项支出科目可按报账规范要求进行具体测算。";
        XSSFRow descRow = sheet.createRow(rowIdx++);
        setCell(descRow, 0, desc, descriptionStyle);
        mergeRegion(sheet, descRow.getRowNum(), descRow.getRowNum(), 0, totalCols - 1);

        // 输出Excel
        String fileName = "附表2-公用经费收支测算附表_" +
                DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(LocalDateTime.now()) + ".xlsx";
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        String encFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encFileName);
        wb.write(response.getOutputStream());
        wb.close();
    }

    // 计算并填充当年成果转化收入计提的成果转化奖行（最终正确版）
    private void calculateAndFillCgzhRow(XSSFSheet sheet, int cgzhRowIdx, int nonFiscalStartCol, int nonFiscalEndCol,
                                         List<XmItem> nonFiscalXmItems, Map<String, CwGyjfsz> cgzhDataMap,
                                         CellStyle style) {
        XSSFRow cgzhRow = sheet.getRow(cgzhRowIdx);
        if (cgzhRow == null) {
            return;
        }

        // 1. 所有列默认填充为 "/"
        int lastCellNum = cgzhRow.getLastCellNum();
        for (int col = 2; col < lastCellNum; col++) {
            setCell(cgzhRow, col, "/", style);
        }

        // 2. 关键修改：基于xmlx字段判断是否为成果转化项目
        int projectCol = nonFiscalStartCol + 2;
        for (XmItem xm : nonFiscalXmItems) {
            // 正确逻辑：判断xmlx是否为"成果转化"，不依赖项目名称
            if ("成果转化".equals(xm.getXmlx())) {
                CwGyjfsz data = cgzhDataMap.get(xm.getName());
                String cgzhj = data != null && data.getCgzhj() != null ? data.getCgzhj().toString() : "0";
                // 在对应项目列填写数值
                setCell(cgzhRow, projectCol, cgzhj, style);
            }
            projectCol++;
        }
    }

    // 计算并填充扣成果转化收入计提的成果转化奖后结余行（最终正确版）
    private void calculateAndFillFinalBalanceRow(XSSFSheet sheet, int finalBalanceRowIdx, int balanceRowIdx,
                                                 int cgzhRowIdx, int fiscalStartCol, int fiscalEndCol,
                                                 int nonFiscalStartCol, int nonFiscalEndCol,
                                                 List<XmItem> nonFiscalXmItems, Map<String, CwGyjfsz> cgzhDataMap,
                                                 CellStyle style) {
        XSSFRow finalBalanceRow = sheet.getRow(finalBalanceRowIdx);
        XSSFRow balanceRow = sheet.getRow(balanceRowIdx);
        XSSFRow cgzhRow = sheet.getRow(cgzhRowIdx);

        if (finalBalanceRow == null || balanceRow == null || cgzhRow == null) {
            return;
        }

        // 1. 所有列默认填充为 "/"
        int lastCellNum = finalBalanceRow.getLastCellNum();
        for (int col = 2; col < lastCellNum; col++) {
            setCell(finalBalanceRow, col, "/", style);
        }

        // 2. 关键修改：基于xmlx字段判断是否为成果转化项目
        int projectCol = nonFiscalStartCol + 2;
        for (XmItem xm : nonFiscalXmItems) {
            // 正确逻辑：判断xmlx是否为"成果转化"
            if ("成果转化".equals(xm.getXmlx())) {
                // 计算结余（当年收支结余 - 成果转化奖）
                double balanceValue = parseDouble(getCellValue(balanceRow, projectCol));
                double cgzhValue = parseDouble(getCellValue(cgzhRow, projectCol));
                double finalValue = balanceValue - cgzhValue;
                // 在对应项目列填写数值
                setCell(finalBalanceRow, projectCol, formatNumber(finalValue), style);
            }
            projectCol++;
        }
    }

    // 计算并填充当年收支结余行
    private void calculateAndFillBalanceRow(XSSFSheet sheet, int balanceRowIdx, int basicIncomeRowIdx,
                                            int totalGongYongRowIdx, int fiscalStartCol, int fiscalEndCol,
                                            int nonFiscalStartCol, int nonFiscalEndCol, CellStyle style) {
        XSSFRow balanceRow = sheet.getRow(balanceRowIdx);
        XSSFRow incomeRow = sheet.getRow(basicIncomeRowIdx);
        XSSFRow totalRow = sheet.getRow(totalGongYongRowIdx);

        if (balanceRow == null || incomeRow == null || totalRow == null) {
            return;
        }

        // 计算合计列（列索引2）
        double incomeTotal = parseDouble(getCellValue(incomeRow, 2));
        double totalExpense = parseDouble(getCellValue(totalRow, 2));
        setCell(balanceRow, 2, formatNumber(incomeTotal - totalExpense), style);

        // 计算财政列
        for (int col = fiscalStartCol; col <= fiscalEndCol; col++) {
            double incomeVal = parseDouble(getCellValue(incomeRow, col));
            double expenseVal = parseDouble(getCellValue(totalRow, col));
            setCell(balanceRow, col, formatNumber(incomeVal - expenseVal), style);
        }

        // 计算非财政列
        for (int col = nonFiscalStartCol; col <= nonFiscalEndCol; col++) {
            double incomeVal = parseDouble(getCellValue(incomeRow, col));
            double expenseVal = parseDouble(getCellValue(totalRow, col));
            setCell(balanceRow, col, formatNumber(incomeVal - expenseVal), style);
        }
    }

    // 计算并填充公用经费总计行
    private void calculateAndFillTotalRow(XSSFSheet sheet, int totalRowIdx, int serviceTotalRowIdx,
                                          int capitalTotalRowIdx, int fiscalStartCol, int fiscalEndCol,
                                          int nonFiscalStartCol, int nonFiscalEndCol, int keshiStartCol,
                                          int keshiEndCol, CellStyle style) {
        XSSFRow totalRow = sheet.getRow(totalRowIdx);
        XSSFRow serviceRow = sheet.getRow(serviceTotalRowIdx);
        XSSFRow capitalRow = sheet.getRow(capitalTotalRowIdx);

        if (totalRow == null || serviceRow == null || capitalRow == null) {
            return;
        }

        // 计算合计列（列索引2）
        double serviceTotal = parseDouble(getCellValue(serviceRow, 2));
        double capitalTotal = parseDouble(getCellValue(capitalRow, 2));
        setCell(totalRow, 2, formatNumber(serviceTotal + capitalTotal), style);

        // 计算财政列
        for (int col = fiscalStartCol; col <= fiscalEndCol; col++) {
            double serviceVal = parseDouble(getCellValue(serviceRow, col));
            double capitalVal = parseDouble(getCellValue(capitalRow, col));
            setCell(totalRow, col, formatNumber(serviceVal + capitalVal), style);
        }

        // 计算非财政列
        for (int col = nonFiscalStartCol; col <= nonFiscalEndCol; col++) {
            double serviceVal = parseDouble(getCellValue(serviceRow, col));
            double capitalVal = parseDouble(getCellValue(capitalRow, col));
            setCell(totalRow, col, formatNumber(serviceVal + capitalVal), style);
        }

        // 计算科室列
        for (int col = keshiStartCol; col <= keshiEndCol; col++) {
            // 只计算金额列（每3列中的第1列），其他列留空
            if ((col - keshiStartCol) % 3 == 0) {
                double serviceVal = parseDouble(getCellValue(serviceRow, col));
                double capitalVal = parseDouble(getCellValue(capitalRow, col));
                setCell(totalRow, col, formatNumber(serviceVal + capitalVal), style);
            } else {
                setCell(totalRow, col, "", style);
            }
        }
    }

    // 辅助方法：获取单元格值
    private String getCellValue(XSSFRow row, int colIdx) {
        XSSFCell cell = row.getCell(colIdx);
        if (cell == null) {
            return "0";
        }
        String value = cell.getStringCellValue();
        return (value == null || value.trim().isEmpty()) ? "0" : value;
    }

    private Map<String, CwGyjfsz> getFinancialDataMap(List<XmItem> xmItems, String jflx, String xmlx, String year) {
        if (xmItems.isEmpty()) {
            return Collections.emptyMap();
        }

        List<String> xmNames = xmItems.stream()
                .map(XmItem::getName)
                .collect(Collectors.toList());

        QueryWrapper<CwGyjfsz> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("jflx", jflx)
                .in("xmmc", xmNames)
                .select("xmmc", "jflx", "xmlx", "cgzhj",  // 确保查询xmlx和cgzhj
                        "bgf", "ysf", "sxf", "sf", "df", "ydf", "qnf", "wyglf",
                        "clf", "ygcjf", "whf", "zlf", "hyf", "pxf", "gwjd",
                        "zycl", "bzgzf", "zyrlf", "lwf", "wtywf", "ghjf",
                        "gwcyc", "qtjtfy", "sj", "qtsp", "fw", "bg", "zy",
                        "jc", "dx", "xx", "wz", "td", "az", "dsfzw", "cq",
                        "gwc", "qtjt", "wwcl", "wxzcgz", "qtzbszc");

        if (xmlx != null && !xmlx.trim().isEmpty()) {
            queryWrapper.eq("xmlx", xmlx.trim());
        }
        if (year != null && !year.trim().isEmpty()) {
            queryWrapper.eq("nd", year.trim());
        }

        List<CwGyjfsz> dataList = cwGyjfszMapper.selectList(queryWrapper);

        return dataList.stream()
                .collect(Collectors.toMap(
                        CwGyjfsz::getXmmc,
                        data -> data,
                        (existing, replacement) -> existing
                ));
    }

    // 根据费用名称和类型获取CwCzjb中的值
    private String getCzjbValue(CwCzjb czjb, String expenseName) {
        if (czjb == null) {
            return "0";
        }
        switch (expenseName) {
            case "办公费":
                return czjb.getBgf() != null ? czjb.getBgf() : "0";
            case "印刷费":
                return czjb.getYs() != null ? czjb.getYs() : "0";
            case "手续费":
                return czjb.getSx() != null ? czjb.getSx() : "0";
            case "水费":
                return czjb.getSf() != null ? czjb.getSf() : "0";
            case "电费":
                return czjb.getDf() != null ? czjb.getDf() : "0";
            case "邮电费":
                return czjb.getYd() != null ? czjb.getYd() : "0";
            case "取暖费":
                return czjb.getQn() != null ? czjb.getQn() : "0";
            case "物业管理费":
                return czjb.getWy() != null ? czjb.getWy() : "0";
            case "差旅费":
                return czjb.getCl() != null ? czjb.getCl() : "0";
            case "因公出国（境）费用":
                return czjb.getCg() != null ? czjb.getCg() : "0";
            case "维修（护）费":
                return czjb.getWx() != null ? czjb.getWx() : "0";
            case "租赁费":
                return czjb.getZl() != null ? czjb.getZl() : "0";
            case "会议费":
                return czjb.getHy() != null ? czjb.getHy() : "0";
            case "培训费":
                return czjb.getPx() != null ? czjb.getPx() : "0";
            case "公务接待费":
                return czjb.getGwjd() != null ? czjb.getGwjd() : "0";
            case "专用材料费":
                return czjb.getZtcl() != null ? czjb.getZtcl() : "0";
            case "被装购置费":
                return czjb.getBz() != null ? czjb.getBz() : "0";
            case "专用燃料费":
                return czjb.getZyrl() != null ? czjb.getZyrl() : "0";
            case "劳务费":
                return czjb.getLw() != null ? czjb.getLw() : "0";
            case "委托业务费":
                return czjb.getWt() != null ? czjb.getWt() : "0";
            case "工会经费":
                return czjb.getGh() != null ? czjb.getGh() : "0";
            case "公务用车运行维护费":
                return czjb.getGw() != null ? czjb.getGw() : "0";
            case "其他交通费用":
                return czjb.getQtjt() != null ? czjb.getQtjt() : "0";
            case "税金及附加费用":
                return czjb.getSj() != null ? czjb.getSj() : "0";
            case "其他商品和服务支出":
                return czjb.getQtsp() != null ? czjb.getQtsp() : "0";
            case "房屋建筑物购建":
                return czjb.getFw() != null ? czjb.getFw() : "0";
            case "办公设备购置":
                return czjb.getBg() != null ? czjb.getBg() : "0";
            case "专用设备购置":
                return czjb.getZy() != null ? czjb.getZy() : "0";
            case "基础设施建设":
                return czjb.getJq() != null ? czjb.getJq() : "0";
            case "大型修缮":
                return czjb.getDx() != null ? czjb.getDx() : "0";
            case "信息网络及软件购置更新":
                return czjb.getXx() != null ? czjb.getXx() : "0";
            case "物资储备":
                return czjb.getWz() != null ? czjb.getWz() : "0";
            case "土地补偿":
                return czjb.getTd() != null ? czjb.getTd() : "0";
            case "安置补助":
                return czjb.getAz() != null ? czjb.getAz() : "0";
            case "地上附着物和青苗补偿":
                return czjb.getDspz() != null ? czjb.getDspz() : "0";
            case "拆迁补偿":
                return czjb.getCq() != null ? czjb.getCq() : "0";
            case "公务用车购置":
                return czjb.getGwc() != null ? czjb.getGwc() : "0";
            case "其他交通工具购置":
                return czjb.getQt() != null ? czjb.getQt() : "0";
            case "文物和陈列品购置":
                return czjb.getWw() != null ? czjb.getWw() : "0";
            case "无形资产购置":
                return czjb.getWxzc() != null ? czjb.getWxzc() : "0";
            case "其他资本性支出":
                return czjb.getZbxzc() != null ? czjb.getZbxzc() : "0";
            default:
                return "0";
        }
    }

    // 根据费用名称获取对应的值
    private String getExpenseValue(CwGyjfsz data, String expenseName) {
        if (data == null) {
            return "0";
        }
        switch (expenseName) {
            case "办公费":
                return data.getBgf() != null ? data.getBgf() : "0";
            case "印刷费":
                return data.getYsf() != null ? data.getYsf() : "0";
            case "手续费":
                return data.getSxf() != null ? data.getSxf() : "0";
            case "水费":
                return data.getSf() != null ? data.getSf() : "0";
            case "电费":
                return data.getDf() != null ? data.getDf() : "0";
            case "邮电费":
                return data.getYdf() != null ? data.getYdf() : "0";
            case "取暖费":
                return data.getQnf() != null ? data.getQnf() : "0";
            case "物业管理费":
                return data.getWyglf() != null ? data.getWyglf() : "0";
            case "差旅费":
                return data.getClf() != null ? data.getClf() : "0";
            case "因公出国（境）费用":
                return data.getYgcjf() != null ? data.getYgcjf() : "0";
            case "维修（护）费":
                return data.getWhf() != null ? data.getWhf() : "0";
            case "租赁费":
                return data.getZlf() != null ? data.getZlf() : "0";
            case "会议费":
                return data.getHyf() != null ? data.getHyf() : "0";
            case "培训费":
                return data.getPxf() != null ? data.getPxf() : "0";
            case "公务接待费":
                return data.getGwjd() != null ? data.getGwjd() : "0";
            case "专用材料费":
                return data.getZycl() != null ? data.getZycl() : "0";
            case "被装购置费":
                return data.getBzgzf() != null ? data.getBzgzf() : "0";
            case "专用燃料费":
                return data.getZyrlf() != null ? data.getZyrlf() : "0";
            case "劳务费":
                return data.getLwf() != null ? data.getLwf() : "0";
            case "委托业务费":
                return data.getWtywf() != null ? data.getWtywf() : "0";
            case "工会经费":
                return data.getGhjf() != null ? data.getGhjf() : "0";
            case "公务用车运行维护费":
                return data.getGwcyc() != null ? data.getGwcyc() : "0";
            case "其他交通费用":
                return data.getQtjtfy() != null ? data.getQtjtfy() : "0";
            case "税金及附加费用":
                return data.getSj() != null ? data.getSj() : "0";
            case "其他商品和服务支出":
                return data.getQtsp() != null ? data.getQtsp() : "0";
            case "房屋建筑物购建":
                return data.getFw() != null ? data.getFw() : "0";
            case "办公设备购置":
                return data.getBg() != null ? data.getBg() : "0";
            case "专用设备购置":
                return data.getZy() != null ? data.getZy() : "0";
            case "基础设施建设":
                return data.getJc() != null ? data.getJc() : "0";
            case "大型修缮":
                return data.getDx() != null ? data.getDx() : "0";
            case "信息网络及软件购置更新":
                return data.getXx() != null ? data.getXx() : "0";
            case "物资储备":
                return data.getWz() != null ? data.getWz() : "0";
            case "土地补偿":
                return data.getTd() != null ? data.getTd() : "0";
            case "安置补助":
                return data.getAz() != null ? data.getAz() : "0";
            case "地上附着物和青苗补偿":
                return data.getDsfzw() != null ? data.getDsfzw() : "0";
            case "拆迁补偿":
                return data.getCq() != null ? data.getCq() : "0";
            case "公务用车购置":
                return data.getGwc() != null ? data.getGwc() : "0";
            case "其他交通工具购置":
                return data.getQtjt() != null ? data.getQtjt() : "0";
            case "文物和陈列品购置":
                return data.getWwcl() != null ? data.getWwcl() : "0";
            case "无形资产购置":
                return data.getWxzcgz() != null ? data.getWxzcgz() : "0";
            case "其他资本性支出":
                return data.getQtzbszc() != null ? data.getQtzbszc() : "0";
            default:
                return "0";
        }
    }

    // 解析费用字段值（格式：金额,内容,依据）
    private String[] parseExpenseField(String fieldValue) {
        if (fieldValue == null || fieldValue.trim().isEmpty()) {
            return new String[]{"0", "", ""};
        }
        // 统一中英文逗号
        String normalized = fieldValue.replace('，', ',');
        String[] parts = normalized.split(",", 3); // 最多分3段
        // 填充默认值
        String amount = parts.length > 0 ? parts[0].trim() : "0";
        String content = parts.length > 1 ? parts[1].trim() : "";
        String basis = parts.length > 2 ? parts[2].trim() : "";
        // 金额非数字则默认为0
        if (!amount.matches("-?\\d+(\\.\\d+)?")) {
            amount = "0";
        }
        return new String[]{amount, content, basis};
    }

    // 根据费用名称获取科室对应的金额、内容和依据
    private String[] getKsExpenseInfo(CwKs ks, String expenseName) {
        if (ks == null) {
            return new String[]{"0", "", ""};
        }
        // 获取原始字段值
        String fieldValue = null;
        switch (expenseName) {
            case "办公费":
                fieldValue = ks.getBgf();
                break;
            case "印刷费":
                fieldValue = ks.getYs();
                break;
            case "手续费":
                fieldValue = ks.getSx();
                break;
            case "水费":
                fieldValue = ks.getSf();
                break;
            case "电费":
                fieldValue = ks.getDf();
                break;
            case "邮电费":
                fieldValue = ks.getYd();
                break;
            case "取暖费":
                fieldValue = ks.getQn();
                break;
            case "物业管理费":
                fieldValue = ks.getWy();
                break;
            case "差旅费":
                fieldValue = ks.getCl();
                break;
            case "因公出国（境）费用":
                fieldValue = ks.getCg();
                break;
            case "维修（护）费":
                fieldValue = ks.getWxf();
                break;
            case "租赁费":
                fieldValue = ks.getZl();
                break;
            case "会议费":
                fieldValue = ks.getHy();
                break;
            case "培训费":
                fieldValue = ks.getPx();
                break;
            case "公务接待费":
                fieldValue = ks.getGwjd();
                break;
            case "专用材料费":
                fieldValue = ks.getZycl();
                break;
            case "被装购置费":
                fieldValue = ks.getBz();
                break;
            case "专用燃料费":
                fieldValue = ks.getZyll();
                break;
            case "劳务费":
                fieldValue = ks.getLw();
                break;
            case "委托业务费":
                fieldValue = ks.getWt();
                break;
            case "工会经费":
                fieldValue = ks.getGh();
                break;
            case "公务用车运行维护费":
                fieldValue = ks.getGwc();
                break;
            case "其他交通费用":
                fieldValue = ks.getQtjtfy();
                break;
            case "税金及附加费用":
                fieldValue = ks.getSj();
                break;
            case "其他商品和服务支出":
                fieldValue = ks.getQtsp();
                break;
            case "房屋建筑物购建":
                fieldValue = ks.getFw();
                break;
            case "办公设备购置":
                fieldValue = ks.getBg();
                break;
            case "专用设备购置":
                fieldValue = ks.getZy();
                break;
            case "基础设施建设":
                fieldValue = ks.getJc();
                break;
            case "大型修缮":
                fieldValue = ks.getDx();
                break;
            case "信息网络及软件购置更新":
                fieldValue = ks.getXx();
                break;
            case "物资储备":
                fieldValue = ks.getWz();
                break;
            case "土地补偿":
                fieldValue = ks.getTd();
                break;
            case "安置补助":
                fieldValue = ks.getAz();
                break;
            case "地上附着物和青苗补偿":
                fieldValue = ks.getDs();
                break;
            case "拆迁补偿":
                fieldValue = ks.getCq();
                break;
            case "公务用车购置":
                fieldValue = ks.getGw();
                break;
            case "其他交通工具购置":
                fieldValue = ks.getQtjt();
                break;
            case "文物和陈列品购置":
                fieldValue = ks.getWw();
                break;
            case "无形资产购置":
                fieldValue = ks.getWx();
                break;
            case "其他资本性支出":
                fieldValue = ks.getQt();
                break;
            default:
                return new String[]{"0", "", ""};
        }
        // 解析字段值
        return parseExpenseField(fieldValue);
    }

    // 写入支出行
    private int writeExpenseRow(XSSFSheet sheet, int rowIdx, String zcType, String econSubject,
                                CellStyle centerStyle, CellStyle leftStyle,
                                int fiscalStartCol, int fiscalEndCol,
                                int nonFiscalStartCol, int nonFiscalEndCol,
                                int keshiStartCol, List<CwKs> keshiList,
                                String contentHint,
                                Map<String, CwGyjfsz> fiscalDataMap, Map<String, CwGyjfsz> nonFiscalDataMap,
                                Map<String, CwCzjb> czjbDataMap,
                                Map<String, CwGyjfsz> cgzhDataMap) { // 新增：成果转化数据Map
        XSSFRow row = sheet.createRow(rowIdx++);
        setCell(row, 0, zcType, centerStyle);
        setCell(row, 1, econSubject, centerStyle);

        // -------------------------- 1. 财政列处理
        int c = fiscalStartCol;
        List<Double> fiscalValues = new ArrayList<>();

        // 填财政基本列
        CwCzjb fiscalCzjb = czjbDataMap.get("财政");
        String fiscalBasicVal = getCzjbValue(fiscalCzjb, econSubject);
        double fiscalBasicNum = parseDouble(fiscalBasicVal);
        fiscalValues.add(fiscalBasicNum);
        setCell(row, c + 1, fiscalBasicVal, centerStyle);

        // 填财政项目列
        for (XmItem xm : fiscalDataMap.keySet().stream()
                .map(name -> new XmItem(name, "财政", "", "")) // 补充第4个参数（jbsrze）
                .collect(Collectors.toList())) {
            CwGyjfsz data = fiscalDataMap.get(xm.getName());
            String projectVal = getExpenseValue(data, econSubject);
            double projectNum = parseDouble(projectVal);
            fiscalValues.add(projectNum);
            c++;
            setCell(row, c + 1, projectVal, centerStyle);
        }

        // 算财政小计
        c = fiscalStartCol;
        double fiscalSubtotal = fiscalValues.stream().mapToDouble(Double::doubleValue).sum();
        setCell(row, c, formatNumber(fiscalSubtotal), centerStyle);

        // -------------------------- 2. 非财政列处理
        c = nonFiscalStartCol;
        List<Double> nonFiscalValues = new ArrayList<>();

        // 填非财政基本列
        CwCzjb nonFiscalCzjb = czjbDataMap.get("非财政");
        String nonFiscalBasicVal = getCzjbValue(nonFiscalCzjb, econSubject);
        double nonFiscalBasicNum = parseDouble(nonFiscalBasicVal);
        nonFiscalValues.add(nonFiscalBasicNum);
        setCell(row, c + 1, nonFiscalBasicVal, centerStyle);

        for (XmItem xm : nonFiscalDataMap.keySet().stream()
                .map(name -> new XmItem(name, "非财政", "", "")) // 补充第4个参数（jbsrze）
                .collect(Collectors.toList())) {
            CwGyjfsz data = nonFiscalDataMap.get(xm.getName());
            String projectVal = getExpenseValue(data, econSubject);
            double projectNum = parseDouble(projectVal);
            nonFiscalValues.add(projectNum);
            c++;
            setCell(row, c + 1, projectVal, centerStyle);
        }

        // 算非财政小计
        c = nonFiscalStartCol;
        double nonFiscalSubtotal = nonFiscalValues.stream().mapToDouble(Double::doubleValue).sum();
        setCell(row, c, formatNumber(nonFiscalSubtotal), centerStyle);

        // -------------------------- 3. 科室列处理
        c = keshiStartCol;
        for (CwKs ks : keshiList) {
            String[] expenseInfo = getKsExpenseInfo(ks, econSubject);
            // 科室金额列
            setCell(row, c++, expenseInfo[0], centerStyle);
            // 测算内容列（无则用提示）
            String csnr = !expenseInfo[1].isEmpty() ? expenseInfo[1] : (contentHint != null ? contentHint : "");
            setCell(row, c++, csnr, leftStyle);
            // 测算依据列
            setCell(row, c++, expenseInfo[2], leftStyle);
        }

        // -------------------------- 4. 合计列处理
        double total = fiscalSubtotal + nonFiscalSubtotal;
        setCell(row, 2, formatNumber(total), centerStyle);

        // --- 新增的特殊逻辑处理 ---
        // 只处理“当年成果转化收入计提的成果转化奖”和“扣成果转化收入计提的成果转化奖后结余”这两行
        if ("当年成果转化收入计提的成果转化奖".equals(econSubject) || "扣成果转化收入计提的成果转化奖后结余".equals(econSubject)) {
            // 遍历所有非财政项目，找到“成果转化项目”
            boolean isCgzhProjectFound = false;
            int projectCol = nonFiscalStartCol + 2; // 从第一个项目列开始
            for (XmItem xm : nonFiscalDataMap.keySet().stream()
                    .map(name -> new XmItem(name, "非财政", "", "")) // 补充第4个参数（jbsrze）
                    .collect(Collectors.toList())) {
                // 检查当前项目是否是“成果转化项目”
                if ("成果转化项目".equals(xm.getName())) {
                    isCgzhProjectFound = true;
                    // 如果是“当年成果转化收入计提的成果转化奖”行
                    if ("当年成果转化收入计提的成果转化奖".equals(econSubject)) {
                        CwGyjfsz data = cgzhDataMap.get(xm.getName());
                        String cgzhj = data != null && data.getCgzhj() != null ? data.getCgzhj().toString() : "0";
                        setCell(row, projectCol, cgzhj, centerStyle);
                        // 同时更新非财政小计和合计列
                        setCell(row, nonFiscalStartCol, cgzhj, centerStyle);
                        setCell(row, 2, cgzhj, centerStyle);
                    }
                    // 如果是“扣成果转化收入计提的成果转化奖后结余”行
                    else if ("扣成果转化收入计提的成果转化奖后结余".equals(econSubject)) {
                        // 从“当年收支结余”行获取非财政小计的值
                        // 假设“当年收支结余”行在当前行上方几行，这里需要根据实际情况调整索引
                        // 更可靠的方法是在计算时直接引用，但为了保持方法独立性，这里采用假设性获取
                        // 实际应用中可能需要传入或通过其他方式获取
                        double balanceValue = 0.0;
                        double cgzhValue = 0.0;

                        // 尝试从当前行的非财政小计列获取“当年收支结余”的值（假设已计算）
                        // 注意：这只是一个假设，实际情况可能需要调整
                        try {
                            balanceValue = parseDouble(getCellValue(row, nonFiscalStartCol));
                        } catch (Exception e) {
                            balanceValue = 0.0;
                        }

                        // 从“当年成果转化收入计提的成果转化奖”行获取成果转化奖的值
                        // 假设“当年成果转化收入计提的成果转化奖”行在当前行上方1行
                        try {
                            XSSFRow cgzhRow = sheet.getRow(row.getRowNum() - 1);
                            cgzhValue = parseDouble(getCellValue(cgzhRow, projectCol));
                        } catch (Exception e) {
                            cgzhValue = 0.0;
                        }

                        double finalBalance = balanceValue - cgzhValue;
                        setCell(row, projectCol, formatNumber(finalBalance), centerStyle);
                        // 同时更新非财政小计和合计列
                        setCell(row, nonFiscalStartCol, formatNumber(finalBalance), centerStyle);
                        setCell(row, 2, formatNumber(finalBalance), centerStyle);
                    }
                } else {
                    // 如果不是“成果转化项目”，则填充为 "/"
                    setCell(row, projectCol, "/", centerStyle);
                }
                projectCol++;
            }

            // 如果没有找到任何“成果转化项目”，则整行相关列都填充为 "/"
            if (!isCgzhProjectFound) {
                setCell(row, 2, "/", centerStyle); // 合计列
                setCell(row, nonFiscalStartCol, "/", centerStyle); // 非财政小计列
                projectCol = nonFiscalStartCol + 2;
                for (int i = 0; i < nonFiscalDataMap.size(); i++) {
                    setCell(row, projectCol++, "/", centerStyle);
                }
            }

            // 财政列和科室列都填充为 "/"
            for (int col = fiscalStartCol; col < nonFiscalStartCol; col++) {
                setCell(row, col, "/", centerStyle);
            }
            c = keshiStartCol;
            for (int i = 0; i < keshiList.size(); i++) {
                setCell(row, c++, "/", centerStyle);
                setCell(row, c++, "", centerStyle); // 测算内容留空
                setCell(row, c++, "", centerStyle); // 测算依据留空
            }
        }

        return rowIdx;
    }

    // 写入分组合计行
    private int writeGroupTotalRow(XSSFSheet sheet, int rowIdx, String leftTitle, String econSubject,
                                   CellStyle totalStyle,
                                   int fiscalStartCol, int fiscalEndCol,
                                   int nonFiscalStartCol, int nonFiscalEndCol,
                                   int keshiStartCol, int keshiCount,
                                   List<Integer> dataRowIndexes, XSSFSheet dataSheet) {
        XSSFRow row = sheet.createRow(rowIdx++);
        setCell(row, 0, leftTitle, totalStyle);
        setCell(row, 1, econSubject == null ? "" : econSubject, totalStyle);

        // 1. 财政列合计
        for (int col = fiscalStartCol; col <= fiscalEndCol; col++) {
            double sum = 0.0;
            for (int dataRowIdx : dataRowIndexes) {
                XSSFRow dataRow = dataSheet.getRow(dataRowIdx);
                if (dataRow != null) {
                    XSSFCell cell = dataRow.getCell(col);
                    if (cell != null) {
                        sum += parseDouble(cell.getStringCellValue());
                    }
                }
            }
            setCell(row, col, formatNumber(sum), totalStyle);
        }

        // 2. 非财政列合计
        for (int col = nonFiscalStartCol; col <= nonFiscalEndCol; col++) {
            double sum = 0.0;
            for (int dataRowIdx : dataRowIndexes) {
                XSSFRow dataRow = dataSheet.getRow(dataRowIdx);
                if (dataRow != null) {
                    XSSFCell cell = dataRow.getCell(col);
                    if (cell != null) {
                        sum += parseDouble(cell.getStringCellValue());
                    }
                }
            }
            setCell(row, col, formatNumber(sum), totalStyle);
        }

        // 3. 科室列合计
        int c = keshiStartCol;
        for (int i = 0; i < keshiCount; i++) {
            double sum = 0.0;
            for (int dataRowIdx : dataRowIndexes) {
                XSSFRow dataRow = dataSheet.getRow(dataRowIdx);
                if (dataRow != null) {
                    XSSFCell cell = dataRow.getCell(c);
                    if (cell != null) {
                        sum += parseDouble(cell.getStringCellValue());
                    }
                }
            }
            setCell(row, c++, formatNumber(sum), totalStyle); // 金额列合计
            setCell(row, c++, "", totalStyle); // 测算内容留空
            setCell(row, c++, "", totalStyle); // 测算依据留空
        }

        // 4. 合计列
        double fiscalTotal = parseDouble(row.getCell(fiscalStartCol).getStringCellValue());
        double nonFiscalTotal = parseDouble(row.getCell(nonFiscalStartCol).getStringCellValue());
        double total = fiscalTotal + nonFiscalTotal;
        setCell(row, 2, formatNumber(total), totalStyle);

        return rowIdx;
    }

    // 写入总计行（公用经费总计、当年收支结余等）
    private int writeTotalRow(XSSFSheet sheet, int rowIdx, String leftTitle, String econSubject,
                              CellStyle centerStyle,
                              int fiscalStartCol, int fiscalEndCol,
                              int nonFiscalStartCol, int nonFiscalEndCol,
                              int keshiStartCol, int keshiEndCol,
                              List<CwKs> keshiList) {
        XSSFRow row = sheet.createRow(rowIdx++);
        setCell(row, 0, leftTitle, centerStyle);
        setCell(row, 1, econSubject == null ? "" : econSubject, centerStyle);
        setCell(row, 2, "", centerStyle);

        // 财政列填0（后续会被计算值覆盖）
        for (int c = fiscalStartCol; c <= fiscalEndCol; c++) {
            setCell(row, c, "0", centerStyle);
        }
        // 非财政列填0（后续会被计算值覆盖）
        for (int c = nonFiscalStartCol; c <= nonFiscalEndCol; c++) {
            setCell(row, c, "0", centerStyle);
        }
        // 科室列（金额0，其他空）
        int c = keshiStartCol;
        for (CwKs ks : keshiList) {
            setCell(row, c++, "0", centerStyle);
            setCell(row, c++, "", centerStyle);
            setCell(row, c++, "", centerStyle);
        }
        return rowIdx;
    }

    // 工具方法：写入标题行
    private int writeTitleRow(XSSFSheet sheet, int rowIdx, int totalCols, String title, CellStyle style) {
        XSSFRow row = sheet.createRow(rowIdx++);
        setCell(row, 0, title, style);
        mergeRegion(sheet, row.getRowNum(), row.getRowNum(), 0, totalCols - 1);
        return rowIdx;
    }

    // 工具方法：写入基本收入总额行
    private int writeBasicIncomeRow(XSSFSheet sheet, int rowIdx, CellStyle centerStyle,
                                    int keshiStartCol, int keshiEndCol,
                                    int fiscalStartCol, int fiscalEndCol,
                                    int nonFiscalStartCol, int nonFiscalEndCol,
                                    List<XmItem> fiscalXmItems, List<XmItem> nonFiscalXmItems,
                                    Map<String, CwCzjb> czjbDataMap) {
        XSSFRow row = sheet.createRow(rowIdx++);
        setCell(row, 0, "基本收入总额", centerStyle);
        setCell(row, 1, "", centerStyle);

        // 处理财政列
        int c = fiscalStartCol;
        double fiscalTotal = 0.0;

        // 从CwCzjb获取财政基本列的jbsrze值
        CwCzjb fiscalCzjb = czjbDataMap.get("财政");
        String fiscalBasicVal = fiscalCzjb != null && fiscalCzjb.getJbsrze() != null ?
                fiscalCzjb.getJbsrze().toString() : "0";
        double fiscalBasicNum = parseDouble(fiscalBasicVal);
        fiscalTotal += fiscalBasicNum;
        setCell(row, c + 1, fiscalBasicVal, centerStyle);

        // 填充财政各项目的jbsrze值并累加
        for (XmItem xm : fiscalXmItems) {
            String jbsrze = xm.getJbsrze() != null ? xm.getJbsrze() : "0";
            double projectNum = parseDouble(jbsrze);
            fiscalTotal += projectNum;
            c++;
            setCell(row, c + 1, jbsrze, centerStyle);
        }

        // 设置财政小计（基本 + 各项目总和）
        c = fiscalStartCol;
        setCell(row, c, formatNumber(fiscalTotal), centerStyle);

        // 处理非财政列
        c = nonFiscalStartCol;
        double nonFiscalTotal = 0.0;

        // 从CwCzjb获取非财政基本列的jbsrze值
        CwCzjb nonFiscalCzjb = czjbDataMap.get("非财政");
        String nonFiscalBasicVal = nonFiscalCzjb != null && nonFiscalCzjb.getJbsrze() != null ?
                nonFiscalCzjb.getJbsrze().toString() : "0";
        double nonFiscalBasicNum = parseDouble(nonFiscalBasicVal);
        nonFiscalTotal += nonFiscalBasicNum;
        setCell(row, c + 1, nonFiscalBasicVal, centerStyle);

        // 填充非财政各项目的jbsrze值并累加
        for (XmItem xm : nonFiscalXmItems) {
            String jbsrze = xm.getJbsrze() != null ? xm.getJbsrze() : "0";
            double projectNum = parseDouble(jbsrze);
            nonFiscalTotal += projectNum;
            c++;
            setCell(row, c + 1, jbsrze, centerStyle);
        }

        // 设置非财政小计（基本 + 各项目总和）
        c = nonFiscalStartCol;
        setCell(row, c, formatNumber(nonFiscalTotal), centerStyle);

        // 计算总计列（财政小计 + 非财政小计）
        setCell(row, 2, formatNumber(fiscalTotal + nonFiscalTotal), centerStyle);

        // 科室列全为"/"
        for (int col = keshiStartCol; col <= keshiEndCol; col++) {
            setCell(row, col, "/", centerStyle);
        }

        return rowIdx;
    }

    // 工具方法：设置单元格值
    private void setCell(XSSFRow row, int colIdx, String val, CellStyle style) {
        XSSFCell cell = row.getCell(colIdx);
        if (cell == null) cell = row.createCell(colIdx);
        cell.setCellValue(val == null ? "" : val);
        if (style != null) cell.setCellStyle(style);
    }

    // 工具方法：合并单元格
    private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    // 工具方法：设置列宽
    private void setColumnWidths(XSSFSheet sheet, int fiscalStart, int fiscalEnd,
                                 int nonFiscalStart, int nonFiscalEnd,
                                 int keshiStart, int keshiCount) {
        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 22 * 256);
        sheet.setColumnWidth(2, 10 * 256);

        // 财政列宽（小计、基本列10字符，项目列16字符）
        for (int c = fiscalStart; c <= fiscalEnd; c++) {
            sheet.setColumnWidth(c, (c - fiscalStart < 2) ? 10 * 256 : 16 * 256);
        }

        // 非财政列宽（同财政列）
        for (int c = nonFiscalStart; c <= nonFiscalEnd; c++) {
            sheet.setColumnWidth(c, (c - nonFiscalStart < 2) ? 10 * 256 : 16 * 256);
        }

        // 科室列宽（金额10字符，内容/依据24字符）
        for (int i = 0; i < keshiCount; i++) {
            sheet.setColumnWidth(keshiStart + i * 3, 10 * 256);
            sheet.setColumnWidth(keshiStart + i * 3 + 1, 24 * 256);
            sheet.setColumnWidth(keshiStart + i * 3 + 2, 24 * 256);
        }
    }

    // 样式：标题样式（16号粗体居中）
    private CellStyle createTitleStyle(XSSFWorkbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 16);
        style.setFont(font);
        return style;
    }

    // 样式：表头样式（灰色背景+粗体+边框）
    private CellStyle createHeaderStyle(XSSFWorkbook wb, boolean isGroup) {
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        // 分组表头（财政/非财政）浅灰，子表头（小计/基本）深灰
        style.setFillForegroundColor((isGroup ? IndexedColors.GREY_25_PERCENT : IndexedColors.GREY_50_PERCENT).getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = wb.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    // 样式：数据样式（边框+居中/左对齐）
    private CellStyle createDataStyle(XSSFWorkbook wb, HorizontalAlignment align) {
        CellStyle style = wb.createCellStyle();
        style.setAlignment(align);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setWrapText(true);
        return style;
    }

    // 样式：合计行样式（深灰背景+粗体+边框）
    private CellStyle createTotalStyle(XSSFWorkbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = wb.createFont();
        font.setBold(true);
        style.setFont(font);
        style.setWrapText(true);
        return style;
    }

    // 样式：说明文字样式（左对齐+自动换行）
    private CellStyle createDescriptionStyle(XSSFWorkbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.TOP);
        style.setWrapText(true);
        return style;
    }

    // 工具方法：字符串转double（空值/非数字返回0）
    private double parseDouble(String value) {
        if (value == null || value.trim().isEmpty()) {
            return 0.0;
        }
        try {
            return Double.parseDouble(value.trim());
        } catch (NumberFormatException e) {
            return 0.0;
        }
    }

    // 工具方法：数字格式化（保留两位小数）
    private String formatNumber(double number) {
        return String.format("%.2f", number);
    }
}